APDL Eductation Resources

home *** CD-ROM | disk | FTP | other *** search

/ APDL Eductation Resources / APDL Eductation Resources.iso / programs / database / powerbase / docs / Calculate < prev next >

Wrap

Text File | 1997-02-21 | 11.2 KB | 241 lines

CH 6 − PERFORMING CALCULATIONS ============================== There are two mechanisms for performing calculations in Powerbase:− • Self-calculating fields which automatically calculate and display a value derived from other fields. These belong to the field class called Computed (see 4.2.1). • Arithmetic performed on a column in a report. Computed fields are of Calculated or Composite types. Calculated fields display a numeric result derived from other fields in the record. When the mouse pointer is moved over such a field it changes into a shape resembling a pocket calculator. Composite fields perform a similar function but display a textual result instead of a number. In this case the mouse pointer changes into a large “+” sign. The latter do not, strictly, involve “calculations” at all but because Composite and Calculated fields work in the same way it is convenient to deal with them together. It isn’t possible to type data directly into either of these special types of field. It is also possible to write your own functions in Basic and use them in Calculated and Composite fields. The system variable TIME$ is useful in this connection. 6.1 Calculated fields ====================== 6.1.1 Simple calculations using Numeric fields ---------------------------------------------- Our first example will be to make a field containing a v.a.t. exclusive price determine the contents of another field which includes v.a.t. at 17% Let the tags of these fields be VEX and VINC respectively. Click MENU over the VINC to bring up the Field submenu and move right over Calculations. You will see a window with a writable icon which contains:− VINC= Complete the formula so that it reads:− VINC=VEX*1.17 and click on OK or type Return. From now on the value in VINC will change whenever you type something in VEX. Since the v.a.t. rate is liable to change it would be better to place the current percentage rate in another Numeric field called VAT and make the formula attached to VINC read:− VINC=VEX+(VEX*VAT/100) The second example calculates the average of four fields No1, No2, No3 and No4, placing the result in a Calculated field AVGE. The required formula is:− AVGE=(No1+No2+No3+No4)/4 A field tag may be entered into the calculation formula by clicking on the required field with Ctrl held down, in the same way as can be done in a search formula (3.3.5). The tag will be entered in the formula at the caret position. 6.1.2 Making calculations retrospective --------------------------------------- The default setting is such that entering or altering a formula affects only those records added or altered after the formula entry/change. The changes can be made retrospective by selecting the Recalculate existing records switch on the formula entry window. On clicking OK you will be asked to confirm that you want previous records to be made consistent with the formula you have just entered. Changes affect the current subfile only, but can be easily implemented in other subfiles by changing to the required subfile, calling up the formula entry window, selecting the option button and clicking OK. 6.1.3 Calculations using non-numeric fields (!) ----------------------------------------------- You might think that the process described is only applicable to numeric fields, but a non-numeric field may be specified in the formula − if it is linked to a validation table. To be of use there must be numeric data in the column of the validation table immediately following the one to which the field is linked (see 5.2). Suppose, for example, we have fields in a student record for A-level exam grades, the field tags being GR1, GR2, GR3, GR4. The grades are non-numeric but they map onto the numeric points system which universities use to control entry. The relationship of grades to points is as follows:− Grade Points A 10 B 8 C 6 D 4 E 2 A validation table could be set up with the grades in column 0 and the equivalent points in column 1. Each of fields GR1−GR4 would be linked to column 0 thus restricting input to the capital letters A-E. A further field of Calculated type would be created to hold the points score. If we associate this field (PTS) with the formula:− PTS=A1+A2+A3+A4. entering or changing the letter grades in A1-A5 will cause the points score to be updated. 6.1.4 Calculations involving times ---------------------------------- There is a field type, Time, which will only accept valid 24-hour times in hh:mm:ss format. If the tag of such a field is included in a calculation formula it will be converted into seconds and the result used in the calculation. Thus a Calculated field DIFF could use two Time fields, TIM1 and TIM2, in the formula:− DIFF=TIM1-TIM2 and Powerbase would keep DIFF updated to show the difference in seconds between the two times. 6.2 Composite fields ===================== To enter the formula for a Composite field follow the same procedure as for a Calculated field. Note that the relevant entry on the Field submenu now says Combine fields. Formula entry is similar to that for Calculated fields except that only string operations are allowed. The one most frequently used will be “+” which allows fields to be joined together. Suppose your database contains fields for surname (SNAM) and forename (FNAM) and you want to be able to print names in the format forename-surname. Define a Composite field called NAME and attach to it the formula:− NAME=FNAM+“ ”+SNAM Note the quoted space separating the names. You might want the NAME field to show only an initial plus the surname, in which case the formula would be:− NAME=LEFT$(FNAM,1)+“. ”+SNAM Composite fields, like Calculated fields, may be used in conjunction with Time fields to perform genuine “clock arithmetic”. Referring to the example in 6.1.4, if we attached the formula to a Composite field instead of a Calculated one it would show the difference in hours, minutes and seconds. The method can be extended to jobs like averaging times, e.g. with a formula such as:− AVE=(TIM1+TIM2+TIM3)/3 As with Calculated fields updating occurs after editing a field whose tag appears in the attached formula. Thus NAME would be updated after changes to FNAM or SNAM and DIFF after changes to TIM1 or TIM2. You can, however, have Composite fields which make use of the Basic system variable TIME$. Thus a field DAY could be linked to the formula:− DAY=LEFT$(TIME$,3) to make it show Mon, Tue etc. No field tags are referred to in the formula so DAY gets updated immediately before displaying the record so that the information is correct at that time. 6.3 User functions =================== These are functions, written in Basic, which accept field tags as parameters and can be included in the calculation formulae of self-calculating fields. All such functions must return only their principal value − RETURN variables in the parameter list aren’t allowed. The name of each function must begin with an upper-case “U”, e.g. FNUaverage. This is to avoid duplication of function-names which are part of Powerbase. All the user functions to be used by a database must be included in a Basic program called UserFuncs which is stored in the database directory (not in the Powerbase directory). When the database is opened UserFuncs will be loaded as a library and Powerbase can call the user functions just as readily as on its own functions. The distribution disc includes a UserFuncs file containing two examples of user functions kindly submitted by David Lenthall. Users are invited to submit other user functions for possible inclusion in the function library. FNUnow (which takes no parameters) operates on the value of TIME$ from the real-time clock and returns a string containing the current date in form DD-MM-YY. A record design could include a field of Composite type, at least eight characters long and tagged as, for example, DATE. Associating the field with the formula:− DATE=FNUnow would cause the field to display the current date at all times. The second function, FNUageinyrs accepts two parameters, each of which should be the tag of a field which holds a date in DD-MM-YY format, and returns the difference between the dates to the nearest year. The first date should be the earlier of the two. If your record design has a field for Date of Birth (let’s call its tag DOB) you can use this function together with FNUnow to make a Composite field (AGE) display a person’s age in years by using the formula:− AGE=FNUageinyrs(DOB,FNUnow) Considerable care is needed in constructing user functions as it is very easy to make Powerbase generate errors. In particular you must avoid giving a function a name which is the same as a field tag − or even one which contains a field tag as a substring. The two functions described could not, for instance, be used in a database which had a field tagged as “now” or “age”, although “NOW” and “AGE” could be used. Calculated and Composite fields are normally updated when changes are made to the fields whose tags appear in their calculation formulae. If a formula contains a user function then the field will be updated after changes to those fields whose tags appear in the parameter list of the function. The previous discussion of Composite fields pointed out that they are updated after altering fields whose tags appear in the calculation formula or, in the case of those using TIME$, immediately before display. There is a problem as a user functions may use TIME$ without it appearing in the parameter list of the function (FNUnow is an example of this). The field AGE in the example given will only be updated when DOB is edited. It will therefore show the age at the time you enter his/her date of birth but won’t automatically change it a year later. To make the change automatic you could introduce TIME$ as a parameter in FNUnow, i.e. make it FNUnow(TIME$) even though the function doesn’t need it. When Powerbase sees TIME$ in the formula it will update AGE. The same trick of using TIME$ as a dummy parameter can be used for any user function associated with a field which you want updating without having to edit the record, but note that the record must be displayed for updating to occur. See 14.5.3 for a method of ensuring that all records in the current subfile are updated on opening the database. 6.4 Calculations on a column of a report ========================================= There is an option on the Print submenu called Numeric fields which is normally shaded. It only becomes available when you highlight one or more Numeric or Calculated fields for printing. You can then access a window listing all the fields (if any) in the record which are of these two types. Associated with each field are six check-boxes which can be selected to include the following in the report:− Count Sum Average Standard deviation Maximum Minimum The check-boxes are shaded until a field is included in a print selection. They then become “live” and any or all of them may be chosen by clicking with SELECT. The information requested is then added to the report footer when printing takes place.